SQL CASE

SQL CASE Description

The CASE SQL expression looks at conditions and returns a value when the first condition is met (for example, an if-then-else statement). So, as soon as the condition is met, the instruction will stop reading and return the result. If no condition is met, the value in the ELSE clause is returned.

If there is no part of ELSE and the conditions are not met, NULL is returned.

CASE Syntax

SELECT column1, column2, ...
CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END

Demonstration database

The following is a sample from the "OrderDetails" table of the "Northwind" database:

SQL CASE Examples

The following SQL statement checks the conditions and returns a value when the first condition is met:

Run SQLSELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails

This SQL query sorts customers by city (City). However, if city is NULL, sort by country (Country):

Run SQLSELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END)